﻿CREATE VIEW [dbo].[BAM_270_271]
AS 
--This correlates 270 and 271 pairs on the BHT03 and TRN02 Identifiers
--Performance can be improved by using CTE
	select BTSMessageID, BTSInterchangeID, MessageID, 'TransactionStatus' = 
     CASE
          WHEN (select count(*) from dbo.BAM_271 b271 where b271.BHT03_SubmitterTransactionIDentifier = b270.BHT03_SubmitterTransactionIDentifier and b270.Subscriber_TRN02_TraceNumber = b271.Subscriber_TRN02_TraceNumber) = 1 THEN 'Completed'
WHEN (select count(*) from dbo.BAM_271 b271 where b271.BHT03_SubmitterTransactionIDentifier = b270.BHT03_SubmitterTransactionIDentifier and b270.Subscriber_TRN02_TraceNumber = b271.Subscriber_TRN02_TraceNumber) > 1 THEN 'Duplicate BHT03 ID'
          ELSE 'Incomplete'
     END, (Select top 1 BTSMessageID from dbo.BAM_271 b271 where b271.BHT03_SubmitterTransactionIDentifier = b270.BHT03_SubmitterTransactionIDentifier and b270.Subscriber_TRN02_TraceNumber = b271.Subscriber_TRN02_TraceNumber) as 'BTSMessageID_271', (Select top 1 BTSInterchangeID from dbo.BAM_271 b271 where b271.BHT03_SubmitterTransactionIDentifier = b270.BHT03_SubmitterTransactionIDentifier and b270.Subscriber_TRN02_TraceNumber = b271.Subscriber_TRN02_TraceNumber) as 'BTSInterchangeID_271'

from BAM_270 b270
;
--drop VIEW [dbo].[BAM_270_271]